<?php
  require_once('../../export/Worksheet.php');
  require_once('../../export/Workbook.php');
  // koneksi ke mysql
  require_once('../../config/koneksi.php');
  require_once('../../config/fungsi_tgl_indo.php');
  
  // function untuk membuat header file excel
  function HeaderingExcel() {
	  $tgl = date("dmY");
      header("Content-type: application/vnd.ms-excel");
      header("Content-Disposition: attachment; filename=Penduduk_$tgl.xls" );
      header("Expires: 0");
      header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
      header("Pragma: public");
      }

  // membuat header file excel dan nama filenya
  HeaderingExcel('Data Penduduk.xls');

  // membuat workbook baru
  $workbook = new Workbook("");
  // membuat worksheet ke-1
  $worksheet1 =& $workbook->add_worksheet('Data Penduduk');

  // setting format header tabel data
  $format =& $workbook->add_format();
  $format->set_align('vcenter');
  $format->set_align('center');
  $format->set_color('white');
  $format->set_bold();
  $format->set_italic();
  $format->set_pattern();
  $format->set_fg_color('green');

  // membuat header tabel dengan format
  $worksheet1->set_row(0, 15);
  $worksheet1->set_column(0, 0, 5);
  $worksheet1->write_string(0, 0, "NO", $format);
  $worksheet1->set_column(0, 1, 20);
  $worksheet1->write_string(0, 1, "NIK", $format);
  $worksheet1->set_column(0, 2, 20);
  $worksheet1->write_string(0, 2, "NOMER KK", $format);
  $worksheet1->set_column(0, 3, 30);
  $worksheet1->write_string(0, 3, "NAMA", $format);
  $worksheet1->set_column(0, 4, 5);
  $worksheet1->write_string(0, 4, "JK", $format);
  $worksheet1->set_column(0, 5, 15);
  $worksheet1->write_string(0, 5, "TEMPAT LAHIR", $format);
  $worksheet1->set_column(0, 6, 15);
  $worksheet1->write_string(0, 6, "TANGGAL LAHIR", $format);
  $worksheet1->set_column(0, 7, 20);
  $worksheet1->write_string(0, 7, "KODE AGAMA", $format);
  $worksheet1->set_column(0, 8, 20);
  $worksheet1->write_string(0, 8, "AGAMA", $format);
  $worksheet1->set_column(0, 9, 20);
  $worksheet1->write_string(0, 9, "KODE PENDDIDIKAN", $format);
  $worksheet1->set_column(0, 10, 20);
  $worksheet1->write_string(0, 10, "PENDIDIKAN", $format);
  $worksheet1->set_column(0, 11, 20);
  $worksheet1->write_string(0, 11, "KODE PEKERJAAN", $format);
  $worksheet1->set_column(0, 12, 20);
  $worksheet1->write_string(0, 12, "PEKERJAAN", $format);
  $worksheet1->set_column(0, 13, 20);
  $worksheet1->write_string(0, 13, "KODE STATUS NIKAH", $format);
  $worksheet1->set_column(0, 14, 20);
  $worksheet1->write_string(0, 14, "STATUS NIKAH", $format);
  $worksheet1->set_column(0, 15, 20);
  $worksheet1->write_string(0, 15, "KODE HUB. KELUARGA", $format);
  $worksheet1->set_column(0, 16, 20);
  $worksheet1->write_string(0, 16, "HUBUNGAN KELUARGA", $format);
  $worksheet1->set_column(0, 17, 20);
  $worksheet1->write_string(0, 17, "KEWARGANEGARAAN", $format);
  $worksheet1->set_column(0, 18, 20);
  $worksheet1->write_string(0, 18, "NOMER PASPOR", $format);
  $worksheet1->set_column(0, 19, 20);
  $worksheet1->write_string(0, 19, "NOMER KITAS", $format);
  $worksheet1->set_column(0, 20, 30);
  $worksheet1->write_string(0, 20, "AYAH", $format);
  $worksheet1->set_column(0, 21, 30);
  $worksheet1->write_string(0, 21, "IBU", $format);
  $worksheet1->set_column(0, 22, 10);
  $worksheet1->write_string(0, 22, "MISKIN", $format);
  $worksheet1->set_column(0, 23, 10);
  $worksheet1->write_string(0, 23, "BPJS", $format);

  // menampilkan data

  $query = "SELECT * FROM penduduk pn inner join agama a on pn.id_agama = a.id_agama inner join pendidikan p on pn.id_pendidikan = p.id_pendidikan inner join pekerjaan pk on pn.id_pekerjaan = pk.id_pekerjaan inner join status_nikah s on pn.id_status = s.id_status inner join hub_keluarga h on pn.id_hub_keluarga = h.id_hub_keluarga order by pn.id_kk";
  $hasil = mysql_query($query);
  $baris = 1;
  while ($data = mysql_fetch_array($hasil))
  {
		$no++;
        $worksheet1->write_number($baris, 0, $no);
        $worksheet1->write_string($baris, 1, $data['nik']);
        $worksheet1->write_string($baris, 2, $data['id_kk']);
		$worksheet1->write_string($baris, 3, $data['nama']);
		$worksheet1->write_string($baris, 4, $data['jk']);
		$worksheet1->write_string($baris, 5, $data['tempat_lahir']);
		$worksheet1->write_string($baris, 6, $data['tgl_lahir']);
		$worksheet1->write_string($baris, 7, $data['id_agama']);
		$worksheet1->write_string($baris, 8, $data['agama']);
		$worksheet1->write_string($baris, 9, $data['id_pendidikan']);
		$worksheet1->write_string($baris, 10, $data['pendidikan']);
		$worksheet1->write_string($baris, 11, $data['id_pekerjaan']);
		$worksheet1->write_string($baris, 12, $data['pekerjaan']);
		$worksheet1->write_string($baris, 13, $data['id_status']);
		$worksheet1->write_string($baris, 14, $data['status_nikah']);
		$worksheet1->write_string($baris, 15, $data['id_hub_keluarga']);
		$worksheet1->write_string($baris, 16, $data['hub_keluarga']);
		$worksheet1->write_string($baris, 17, $data['kewarganegaraan']);
		$worksheet1->write_string($baris, 18, $data['nopaspor']);
		$worksheet1->write_string($baris, 19, $data['nokitas']);
		$worksheet1->write_string($baris, 20, $data['ayah']);
		$worksheet1->write_string($baris, 21, $data['ibu']);
		$worksheet1->write_string($baris, 22, $data['is_miskin']);
		$worksheet1->write_string($baris, 23, $data['bpjs']);
        $baris++;
  }

  // membuat worksheet ke-2
  $worksheet2 =& $workbook->add_worksheet('Data Agama');

  // membuat header tabel
  $worksheet2->set_row(0, 15);
  $worksheet2->set_column(0, 0, 5);
  $worksheet2->write_string(0, 0, "KODE", $format);
  $worksheet2->set_column(0, 1, 30);
  $worksheet2->write_string(0, 1, "AGAMA", $format);

  // menampilkan data

  $query = "SELECT * FROM agama order by id_agama";
  $hasil = mysql_query($query);
  $baris = 1;
  while ($data = mysql_fetch_array($hasil))
  {
        $worksheet2->write_number($baris, 0, $data['id_agama']);
        $worksheet2->write_string($baris, 1, $data['agama']);
        $baris++;
  }
  
  // membuat worksheet ke-3
  $worksheet3 =& $workbook->add_worksheet('Data Pendidikan');

  // membuat header tabel
  $worksheet3->set_row(0, 15);
  $worksheet3->set_column(0, 0, 5);
  $worksheet3->write_string(0, 0, "KODE", $format);
  $worksheet3->set_column(0, 1, 30);
  $worksheet3->write_string(0, 1, "PENDIDIKAN", $format);

  // menampilkan data

  $query = "SELECT * FROM pendidikan order by id_pendidikan";
  $hasil = mysql_query($query);
  $baris = 1;
  while ($data = mysql_fetch_array($hasil))
  {
        $worksheet3->write_number($baris, 0, $data['id_pendidikan']);
        $worksheet3->write_string($baris, 1, $data['pendidikan']);
        $baris++;
  }

  // membuat worksheet ke-4
  $worksheet4 =& $workbook->add_worksheet('Data Pekerjaan');

  // membuat header tabel
  $worksheet4->set_row(0, 15);
  $worksheet4->set_column(0, 0, 5);
  $worksheet4->write_string(0, 0, "KODE", $format);
  $worksheet4->set_column(0, 1, 30);
  $worksheet4->write_string(0, 1, "PEKERJAAN", $format);

  // menampilkan data

  $query = "SELECT * FROM pekerjaan order by id_pekerjaan";
  $hasil = mysql_query($query);
  $baris = 1;
  while ($data = mysql_fetch_array($hasil))
  {
        $worksheet4->write_number($baris, 0, $data['id_pekerjaan']);
        $worksheet4->write_string($baris, 1, $data['pekerjaan']);
        $baris++;
  }
  
  // membuat worksheet ke-4
  $worksheet5 =& $workbook->add_worksheet('Data Status Nikah');

  // membuat header tabel
  $worksheet5->set_row(0, 15);
  $worksheet5->set_column(0, 0, 5);
  $worksheet5->write_string(0, 0, "KODE", $format);
  $worksheet5->set_column(0, 1, 30);
  $worksheet5->write_string(0, 1, "Status Nikah", $format);

  // menampilkan data

  $query = "SELECT * FROM status_nikah order by id_status";
  $hasil = mysql_query($query);
  $baris = 1;
  while ($data = mysql_fetch_array($hasil))
  {
        $worksheet5->write_number($baris, 0, $data['id_status']);
        $worksheet5->write_string($baris, 1, $data['status_nikah']);
        $baris++;
  }
  
  // membuat worksheet ke-5
  $worksheet6 =& $workbook->add_worksheet('Data Hubungan Keluarga');

  // membuat header tabel
  $worksheet6->set_row(0, 15);
  $worksheet6->set_column(0, 0, 5);
  $worksheet6->write_string(0, 0, "KODE", $format);
  $worksheet6->set_column(0, 1, 30);
  $worksheet6->write_string(0, 1, "Hubungan Keluarga", $format);

  // menampilkan data

  $query = "SELECT * FROM hub_keluarga order by id_hub_keluarga";
  $hasil = mysql_query($query);
  $baris = 1;
  while ($data = mysql_fetch_array($hasil))
  {
        $worksheet6->write_number($baris, 0, $data['id_hub_keluarga']);
        $worksheet6->write_string($baris, 1, $data['hub_keluarga']);
        $baris++;
  }
  
  $workbook->close();
?>